import datetime

import pandas as pd
from sklearn.preprocessing import OneHotEncoder

from src.tablet.preprocessor import prop_preprocessor as prop
from src.tablet.preprocessor.model_data_preprocessor import preprocess_model_data
from src.tablet.model.tablet_model_config import MODEL_DAYS, EVN_TEMPLATE_ID
from src.utils.config import logger
from src.utils.db_processor import presto_processor
from src.utils.util import get_today, check_date_str, format_date_string,check_dated_str


SETTLE_DATA = """
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int) AS item_quotation_price_num,
case when  psdp.settle_document_create_date<=date'2023-01-17' then cast(psdp.settle_document_create_date+interval'9' day as date) 
else cast(psdp.settle_document_create_date as date) end settle_list_create_date,
cdp.create_date AS shop_out_date,
dp.product_name,
cast(dp.product_level_template_id as int) as product_level_template_id,
cast(dps.product_sku_id as int) AS product_sku_key,
pssp.property_name AS price_property_name_name,
pssp.property_value_name AS price_property_value_name,
dp.product_brand_name,
cdp.product_combine_level_name AS product_level_name,
cdp.product_no
FROM dw.dw_centre_document_product cdp
JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
JOIN dw.dw_platform_document_product pdp ON pdp.product_no = psdp.product_no AND pdp.quotation_document_no = psdp.quotation_document_no
JOIN dim.dim_product_sku dps ON dps.product_sku_id = cdp.product_combine_sku_id
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
LEFT JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286)
AND pdp.quotation_document_type_id = 10
and cdp.create_date not between  date'2023-01-17' and date'2023-01-25'
AND cdp.create_date >= cast('{}' as date)
"""


def load_settle_data(model_date=None):
    """
    读取结算数据
    :param model_date: 建模日期，如果为None，默认为当天
    :return:
    """
    if model_date is None:
        model_date = get_today()
    else:
        model_date = check_date_str(model_date)
    start_date = model_date - datetime.timedelta(days=MODEL_DAYS)
    d_start = datetime.datetime.strptime('2023-01-25', '%Y-%m-%d')
    if(start_date<d_start):
        diff=MODEL_DAYS+9
        start_date = model_date - datetime.timedelta(days=diff)
    logger.info('loading settle data from {}'.format(start_date))
    start_date = format_date_string(start_date)
    sql = SETTLE_DATA.format(start_date)
    data = presto_processor.load_sql(sql, parse_dates=['settle_list_create_date', 'shop_out_date'])
    # data['settle_list_create_date'] = data['settle_list_create_date'].apply(check_dated_str)
    # data['shop_out_date'] = data['shop_out_date'].apply(check_dated_str)

    data['product_level_template_id'] = data['product_level_template_id'].astype(str)
    # 去掉环保机
    data = data[data['product_level_template_id'] != EVN_TEMPLATE_ID]
    # 将无属性-属性值的机器填充为unknown
    data = data.fillna('unknown')
    return data


def preprocess_settle_data(data=None, model_date=None):
    """
    预处理结算数据
    :param data: 结算原始数据，如果为None，那么先读取结算数据
    :param model_date: 建模日期
    :return:
    """
    if data is None:
        data = load_settle_data(model_date)

    data['price_property_name_name'] = data['price_property_name_name'].map(lambda x: x.strip())
    product_ohe = OneHotEncoder(handle_unknown='ignore')
    product_ohe.fit(data[['product_name']])
    data = data[data['price_property_name_name'].isin(prop.PROP_LIST)]

    # 将长数据转为宽数据
    pivot_data = data.pivot_table(index=['document_item_id'],
                                  columns='price_property_name_name',
                                  values='price_property_value_name', aggfunc=lambda x: x.unique())
    pivot_data = pivot_data.fillna('unknown')
    pivot_data = pivot_data.reset_index()

    data = data.drop(columns=['price_property_name_name', 'price_property_value_name']).drop_duplicates()
    data = pd.merge(data, pivot_data, on='document_item_id')

    model_data = preprocess_model_data(data)

    return model_data, product_ohe
